<?php
namespace app\lib\util;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class excel {

    /*
     * 把导出为excel到浏览器并下载
     * $title:标题
     * $headArr: 表头
     * $data: 数据
     * $fileName:文件名
     * */
    function excelExport($title="",$headArr = [], $data = [],$fileName = '') {
        $fileName       .= "_" . date("Y_m_d", time()) . ".xls";
        $spreadsheet    = new Spreadsheet();

        $objPHPExcel    = $spreadsheet->getActiveSheet();
        $objPHPExcel->setTitle($title);
        $objPHPExcel->setCellValueByColumnAndRow(1, 1, $title);
        // 设置表头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setCellValue($colum . '1', $v);
            $key += 1;
        }

        $column = 2;
        foreach ($data as $key => $rows) { // 行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) { // 列写入
                $objPHPExcel->setCellValue(chr($span) . $column, $value);
                $span++;
            }
            $column++;
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除临时的sheet
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}